package org.kjtc.mapper;

import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.jdbc.SQL;
import org.kjtc.entity.BackLog;
import org.kjtc.entity.FeedBack;
import org.kjtc.entity.Log;

import java.util.Date;

/**
 * Created by Administrator on 2017/11/27 0027.
 */
public class AllProvider {

    public String getLogMessage(Log log){
        SQL sql = new SQL().SELECT("LogContext,Date,UserName").FROM("log").WHERE("1=1");
        if(log.getLogContext() != "" && log.getLogContext() != null){
            sql.WHERE("LogContext LIKE '%"+log.getLogContext()+"%'");
        }
        if(log.getBeginDate() != "" && log.getBeginDate() != null){
            sql.WHERE("date > #{BeginDate}");
        }
        if(log.getEndDate() != "" && log.getEndDate() != null){
            sql.WHERE("date < #{EndDate}");
        }
        sql.ORDER_BY("Date desc");
        return sql.toString();
    }

    public String getBackLog(@Param("productVersionId")String productVersionId
            ,@Param("MemberId")String MemberId,@Param("BacklogStatus")String BacklogStatus) {
        SQL sql = new SQL().SELECT("md.ModuleName,bl.BacklogID,bl.BacklogName,wt.WorkTypeNameSign,bl.BacklogStatus\n" +
                ",ss.StateName,ifnull(bl.ActualWorkDate,0) ActualWorkDate,bl.ExpectedStartDTTM,bl.ExpectedWorkDate,TO_DAYS(bl.ExpectedEndDTTM)-TO_DAYS(NOW()) TimeSituation\n" +
                ",bl.ActualStartDTTM,bl.ActualEndDTTM").FROM("backlog bl")
                .LEFT_OUTER_JOIN("module md ON md.ModuleID=bl.ModuleID")
                .LEFT_OUTER_JOIN("worktype wt ON bl.WorkTypeID=wt.WorkTypeID")
                .LEFT_OUTER_JOIN("state ss ON bl.BacklogStatus=ss.StateID");
        if (MemberId != null && MemberId !="") {
            sql.WHERE("bl.UserID=#{MemberId}");
        }
        if (productVersionId != null && productVersionId !="") {
            sql.WHERE("md.ProductVersionID=#{productVersionId}");
        }
        if (BacklogStatus != null && BacklogStatus !="") {
            sql.WHERE("bl.BacklogStatus=#{BacklogStatus}");
        }else{
            sql.WHERE("bl.BacklogStatus!=0");
        }
        sql.ORDER_BY("bl.BacklogStatus");
        return sql.toString();
    }

    public String getBackLogByUser(BackLog backLog) {
        SQL sql = new SQL().SELECT("md.ModuleName,bl.BacklogID,bl.BacklogName,wt.WorkTypeNameSign,bl.BacklogStatus\n" +
                ",ss.StateName,ifnull(bl.ActualWorkDate,0) ActualWorkDate,bl.ExpectedStartDTTM,bl.ExpectedStartDTTM,bl.ExpectedWorkDate,TO_DAYS(bl.ExpectedEndDTTM)-TO_DAYS(NOW()) TimeSituation\n" +
                ",bl.ActualStartDTTM,bl.ActualEndDTTM").FROM("backlog bl")
                .LEFT_OUTER_JOIN("module md ON md.ModuleID=bl.ModuleID")
                .LEFT_OUTER_JOIN("worktype wt ON bl.WorkTypeID=wt.WorkTypeID")
                .LEFT_OUTER_JOIN("state ss ON bl.BacklogStatus=ss.StateID");
        if (backLog.getUserID() != null && backLog.getUserID() !="") {
            sql.WHERE("bl.UserID=#{UserID}");
        }
        if (backLog.getProductVersionID() != null && backLog.getProductVersionID() !="") {
            sql.WHERE("md.ProductVersionID = #{ProductVersionID}");
        }
        if(backLog.getBackLogStatus() != null){
            sql.WHERE("bl.BacklogStatus = #{BackLogStatus}");
        }
        sql.ORDER_BY("bl.BacklogStatus");
        return sql.toString();
    }
    public String getBackLogFeed(String backLogId){
        SQL sql = new SQL().SELECT("u.UserName,f.*").FROM("feedback f,module m,backlog b,userinfo u")
                .WHERE("f.FeedBackStatus='0' and f.ModuleID=m.ModuleID and m.ModuleID=b.ModuleID " +
                        "and u.UserID=f.FeedBackUser");
        if(backLogId=="" && backLogId==""){
            sql.WHERE("b.BacklogID in ('')");
        }else{
            sql.WHERE("b.BacklogID in (" + backLogId +")");
        }
                sql.GROUP_BY("f.FeedBackID");
        return sql.toString();
    }
    public String getBackLogProductVersionId(@Param("productVersionId")String productVersionId){
        SQL sql = new SQL().SELECT("md.ModuleName,bl.BacklogID,bl.BacklogName,wt.WorkTypeNameSign,bl.BacklogStatus\n" +
                ",ss.StateName,ifnull(bl.ActualWorkDate,0) ActualWorkDate,bl.ExpectedStartDTTM,bl.ExpectedWorkDate,TO_DAYS(bl.ExpectedEndDTTM)-TO_DAYS(NOW()) TimeSituation\n" +
                ",bl.ActualStartDTTM,bl.ActualEndDTTM").FROM("backlog bl")
                .LEFT_OUTER_JOIN("module md ON md.ModuleID=bl.ModuleID")
                .LEFT_OUTER_JOIN("worktype wt ON bl.WorkTypeID=wt.WorkTypeID")
                .LEFT_OUTER_JOIN("state ss ON bl.BacklogStatus=ss.StateID");
        if (productVersionId != null && productVersionId !="") {
            sql.WHERE("md.ProductVersionID=#{productVersionId}");
        }
        sql.WHERE("bl.BacklogStatus != 0");
        sql.ORDER_BY("bl.BacklogStatus");
        return sql.toString();
    }
    public String getAllBack(String userId){
        SQL sql = new SQL().SELECT("md.ModuleName,pd.ProductName,pv.ProductVersionName,bl.BacklogName,ifnull(bl.ActualWorkDate,0) ActualWorkDate " +
                ",bl.BacklogStatusName").FROM("product pd")
                .LEFT_OUTER_JOIN("productversion pv on pd.ProductID=pv.ProductID")
                .LEFT_OUTER_JOIN("module md ON md.ProductVersionID=pv.ProductVersionID")
                .LEFT_OUTER_JOIN("backlog bl ON bl.ModuleID=md.ModuleID").WHERE("BacklogName!=''");
        if (userId != null && userId !="") {
            sql.WHERE("bl.UserID=#{userId}");
        }
        sql.ORDER_BY("bl.BacklogStatus");
        return sql.toString();
    }

    public String getVersionDetalis(BackLog backLog){
        SQL sql = new SQL().SELECT("m.ModuleName,s.StateName,m.ModuleID,m.ProductVersionID,v.ProductVersionName,p.ProductID,p.ProductName,\n" +
                "  v.ProductVersionDescription ,u.UserName, w.WorkTypeID, w.WorkTypeName,b.BacklogID, b.BacklogName,\n" +
                "  b.BacklogStatus, b.ExpectedWorkDate ").FROM("module m ")
                .LEFT_OUTER_JOIN("productversion v ON v.ProductVersionID = m.ProductVersionID")
                .LEFT_OUTER_JOIN("product p ON p.ProductID = v.ProductID")
                .LEFT_OUTER_JOIN("backlog b ON m.ModuleID = b.ModuleID")
                .LEFT_OUTER_JOIN("worktype w ON b.WorkTypeID = w.WorkTypeID")
                .LEFT_OUTER_JOIN("userinfo u ON u.UserID = b.UserID")
                .LEFT_OUTER_JOIN("state s ON b.BacklogStatus = s.StateID");
        if(backLog.getModuleIDList() == "" && backLog.getModuleIDList() != null){
            sql.WHERE("v.ProductVersionID  = #{ProductVersionID} and v.ProductID= #{ProductID} and m.ModuleID in (#{ModuleIDList})");
        }
        sql.WHERE("b.BacklogStatus != 0");
        sql.ORDER_BY("b.ModuleID, b.workTypeID");
        return sql.toString();
    }

    public String getFeedBackDetalis(FeedBack feedback){
        SQL sql = new SQL().SELECT("m.ModuleID,m.ModuleName,u.UserName,f.* ").FROM("feedback f ")
                .LEFT_OUTER_JOIN("module m ON m.ModuleID = f.ModuleID")
                .LEFT_OUTER_JOIN("productversion v ON v.ProductVersionID = m.ProductVersionID")
                .LEFT_OUTER_JOIN("product p ON p.ProductID = v.ProductID")
                .LEFT_OUTER_JOIN("userinfo u ON f.FeedBackUser = u.UserID");
        if(feedback.getModuleIDList() == "" && feedback.getModuleIDList() != null){
            sql.WHERE("v.ProductVersionID  = #{ProductVersionID} and v.ProductID= #{ProductID} and m.ModuleID in (#{ModuleIDList})");
        }
        sql.ORDER_BY("m.ModuleID");
        return sql.toString();
    }

    public String doGetBackLogMsgOne(BackLog backLog){
        SQL sql = new SQL().SELECT("u.UserName,pr.ProductID,pr.ProductName,p.ProductVersionID,p.ProductVersionName,\n" +
                "\t m.ModuleID, m.ModuleName, b.BacklogID,b.BacklogName,s.StateName,b.ExpectedWorkDate").FROM("backlog b ")
                .LEFT_OUTER_JOIN("module m ON m.ModuleID = b.ModuleID")
                .LEFT_OUTER_JOIN("productversion p ON p.ProductVersionID= m.ProductVersionID")
                .LEFT_OUTER_JOIN("product pr ON pr.ProductID = p.ProductID")
                .LEFT_OUTER_JOIN("state s ON s.StateID = b.BacklogStatus")
                .LEFT_OUTER_JOIN("userinfo u ON u.UserID = b.UserID");
        if (backLog.getActualStartDTTM() != null && backLog.getActualEndDTTM() != null) {
            if(backLog.getActualStartDTTM().equals(backLog.getActualEndDTTM())){
                sql.WHERE("b.ExpectedEndDTTM = #{ActualEndDTTM}");
            }else{
                sql.WHERE("b.ExpectedEndDTTM >= #{ActualStartDTTM} and b.ExpectedEndDTTM <= #{ActualEndDTTM}");
            }
        }
        if (backLog.getUserID() != null && backLog.getUserID() != "") {
            sql.WHERE("b.UserID = #{UserID} ");
        }
            sql.WHERE("s.StateID in (1,2)");
        sql.ORDER_BY("pr.ProductID");
        return sql.toString();
    }

    public String doGetBackLogMsgTwo(BackLog backLog){
        SQL sql = new SQL().SELECT("u.UserName,pr.ProductID,pr.ProductName,p.ProductVersionID,p.ProductVersionName,\n" +
                "\t m.ModuleID, m.ModuleName, b.BacklogID,b.BacklogName,s.StateName,b.ExpectedWorkDate").FROM("backlog b ")
                .LEFT_OUTER_JOIN("module m ON m.ModuleID = b.ModuleID")
                .LEFT_OUTER_JOIN("productversion p ON p.ProductVersionID= m.ProductVersionID")
                .LEFT_OUTER_JOIN("product pr ON pr.ProductID = p.ProductID")
                .LEFT_OUTER_JOIN("state s ON s.StateID = b.BacklogStatus")
                .LEFT_OUTER_JOIN("userinfo u ON u.UserID = b.UserID");
        if (backLog.getActualStartDTTM() != null && backLog.getActualEndDTTM() != null) {
            if(backLog.getActualStartDTTM().equals(backLog.getActualEndDTTM())){
                sql.WHERE("b.ActualEndDTTM = #{ActualEndDTTM}");
            }else{
                sql.WHERE("b.ActualEndDTTM >= #{ActualStartDTTM} and b.ActualEndDTTM <= #{ActualEndDTTM}");
            }
        }
        if (backLog.getUserID() != null && backLog.getUserID() != "") {
            sql.WHERE("b.UserID = #{UserID} ");
        }
        sql.WHERE("s.StateID in (3,4)");
        sql.ORDER_BY("pr.ProductID");
        return sql.toString();
    }
    public String getProductNameByProductVersionID(String ProductVersionID){
        SQL sql = new SQL().SELECT("productName").FROM("product").WHERE("1 = 1");
        if(ProductVersionID != null && ProductVersionID != ""){
            sql.WHERE("ProductID = (SELECT ProductID FROM productversion WHERE ProductVersionID=#{ProductVersionID})");
        }
           return sql.toString();
    }

}
